import MySQLdb as mysql
import mysql_config as dbconfig

class Basic:
    def __init__(self):
        self.connection = mysql.connect(host=dbconfig.host, db=dbconfig.db, port=dbconfig.port, user=dbconfig.user, passwd=dbconfig.password)    
        self.conn = self.connection.cursor()
        
    def insert(self, table_name, field):
        key, value_ref = self.__insert_field_process(field)                  
        values = field.values()   
        sql_statement = "INSERT INTO " + table_name + " " + key + " VALUES " + value_ref
        print sql_statement, values 
        self.conn.execute(sql_statement, values)
        self.connection.commit()
    
    def query_all(self, table_name):
        sql_statement = "SELECT * FROM " + table_name
        print sel_statement
        self.conn.execute(sql_statement)
        result_set = self.conn.fetchall()
        return result_set
    
    def query(self, table_name, field, quantity=0):
        '''
        Notice:the parameter field must be a list such as ['a'],['a','b'], MUST NOT BE a tuple.
        there will have some bugs if the field you give is a tuple.
        e.g:
            if field = ('china'),then the sql_statement will be something like this:
                SELECT c,h,i,n,a FROM data-----absolutely it's a bug
            if field =['china'],then the sql_statement is:
                SELECT china FROM data-----it's the statement what we expect
        '''
        key = ""
        for i in field:
            key += i
            key += ","
        key = key[0:len(key) - 1]
        sql_statement = "SELECT " + key + " FROM " + table_name
        print sql_statement
        self.conn.execute(sql_statement)
        if quantity == 0:
            result_set = self.conn.fetchall()
        else:
            result_set = self.conn.fetchmany(quantity)
        return result_set
    
    def update(self):
        pass
        
    def __insert_field_process(self, field):
        '''
        The parameter field is a dictionary, such as {"a":"apple","d":"dog"},so i'm going to make \
        it into a string
        '''
        keys = field.keys() 
        key = ""
        value_ref = ""
        for i in keys:
            key += i
            value_ref += "%s," 
            key += ","
        key = key[0:len(key) - 1]
        value_ref = value_ref[0:len(value_ref) - 1]
        key = "(" + key + ")"
        value_ref = "(" + value_ref + ")"
        return key, value_ref
    
    def close(self):
        if(self.connection != None):
            self.connection.close()

#Testing codes      
class GPSdata(Basic):
    def __init__(self):
        Basic.__init__(self)
        self.table_name = 'gps_data'
        
    def insert(self, field):
        Basic.insert(self, self.table_name, field)
    
    def query_all(self):
        result_set = Basic.query_all(self, self.table_name)
        return result_set
        
    def query(self, field, quantity=0):
        result_set = Basic.query(self, self.table_name, field, quantity)
        return result_set

def message_process(msg):
    dit = { }
    msg_list = msg.split(';')
    for msg_unit in msg_list:
        key_value = msg_unit.split(':')
        key = key_value[0].strip()
        value = key_value[1].strip()
        if key.strip() != "address".strip():
            dit[key] = float(value)
        else:
            dit[key] = value
    return dit

if __name__ == "__main__":
    #dit = {"latitude":"2", "longitude":"2", "altitude":"88" , "address":"guangzhou,tianhe,SCUT"}
    #key = dit.keys()
    #value = dit.values()
    #basic = Basic()
    #basic.insert("gps_data", dit)
    #b = Basic()
    #rst = b.query('gps_data', ('latitude', 'longitude', 'address'), 2)
    #print rst
    msg = "   latitude:24.123456789123456876;longitude:25;altitude:30.123244;address:SCUT,Wushan,Tianhe,Mainland China"
    dit = message_process(msg)
    
    gps = GPSdata()
    gps.insert(dit)
    #rst = gps.query(['latitude'])
    #print rst
    #rst = gps.query(['latitude','longitude'],3)
    #print rst

